-- create new database 
USE [master]

GO

-- check for database already exists 
IF NOT EXISTS (SELECT *
               FROM   master.dbo.sysdatabases
               WHERE  name = 'IMS')
  BEGIN
      CREATE DATABASE [IMS] ON PRIMARY ( NAME = N'IMS_Data', FILENAME = N'C:\Temp\SQLData\ims_data.mdf', SIZE = 167872KB, MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB ) LOG ON ( NAME = N'IMS_Log', FILENAME = N'C:\Temp\SQLData\ims_Log.ldf', SIZE = 2048KB, MAXSIZE = 2048GB, FILEGROWTH = 16384KB )
  END

GO

-- use ims now.
USE [IMS]

/********************* CREATION OF TABLES *************************************************/
-- drop constraint for category id 
IF EXISTS (SELECT *
           FROM   sys.foreign_key_columns
           WHERE  constraint_object_id = Object_id(N'[dbo].[FK_Product_Category_CategoryId]')
                  AND referenced_object_id = Object_id(N'[dbo].[Category]'))
  ALTER TABLE [dbo].[Product]
    DROP CONSTRAINT [FK_Product_Category_CategoryId]

GO

-- check for exists 
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].[category]')
                  AND type IN ( N'U' ))
  BEGIN
      DROP TABLE Category
  END

-- Category Table
CREATE TABLE Category
  (
     [CategoryId]  INT NOT NULL IDENTITY(1, 1),
     [Name]        NVARCHAR(250) NOT NULL,
     [Description] NVARCHAR(1024) NULL,
     [CreateDate]  DATETIME NOT NULL,
     [UpdateDate]  DATETIME NULL,
     [Active]      BIT NOT NULL
  )

-- Primary Key  
ALTER TABLE Category
  ADD CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED (CategoryId ASC)

go

-- DF Create date
ALTER TABLE Category
  ADD CONSTRAINT [dbo_Category_CreateDate] DEFAULT (Getdate()) FOR CreateDate

go

-- DF Active
ALTER TABLE Category
  ADD CONSTRAINT [DF_Category_Active] DEFAULT ((1)) FOR [Active]

go

--- Product table
IF EXISTS (SELECT *
           FROM   sys.objects
           WHERE  object_id = Object_id(N'[dbo].[Product]')
                  AND type IN ( N'U' ))
  BEGIN
      DROP TABLE Product
  END

CREATE TABLE Product
  (
     [ProductId]    INT NOT NULL IDENTITY(1, 1),
     [Name]         NVARCHAR(250) NOT NULL,
     [Description]  NVARCHAR(1024) NULL,
     [CategoryId]   INT NOT NULL,
     [Price]        DECIMAL(10, 2) NOT NULL,
     [CreateDate]   DATETIME NOT NULL,
     [UpdateDate]   DATETIME NULL,
     [Active]       BIT NOT NULL,
     [VersionStamp] TIMESTAMP NOT NULL
  )

go

-- Primary Key  
ALTER TABLE Product
  ADD CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED (ProductId ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

go

-- DF Create DAte 
ALTER TABLE Product
  ADD CONSTRAINT [dbo_Product_CreateDate] DEFAULT ((Getdate())) FOR CreateDate

go

-- DF Active
ALTER TABLE Product
  ADD CONSTRAINT [dbo_Product_Active] DEFAULT ((1)) FOR Active

go

--FK CategoryId
ALTER TABLE Product
  ADD CONSTRAINT [FK_Product_Category_CategoryId] FOREIGN KEY ([CategoryId]) REFERENCES Category ([CategoryId]) ON DELETE NO ACTION ON UPDATE NO ACTION;

go
/*****************
-- Drop existing column
  IF EXISTS (SELECT * FROM sys.columns WHERE  Name = N'Drained' AND Object_ID = Object_id(N'Publish.SignProduct'))
  BEGIN
      ALTER TABLE [Publish].[SignProduct]  DROP COLUMN [Drained]
  END 

  -- sign product column drop is nullable then drop 
  IF EXISTS (SELECT * FROM sys.columns WHERE  Name = N'UpdateDate' AND Object_ID = Object_id(N'Publish.SignNote') AND is_nullable=1)
  BEGIN
      ALTER TABLE [Publish].[SignNote]  DROP COLUMN [UpdateDate]
  END 
 
 --- drop userid column if it has uniqueidentifier datatype
IF EXISTS (SELECT *
           FROM   INFORMATION_SCHEMA.COLUMNS
           WHERE  TABLE_SCHEMA = 'WORKFLOW'
                  AND TABLE_NAME = 'Audit'
                  AND DATA_TYPE = 'uniqueidentifier'
				  AND COLUMN_NAME='UserId')
  BEGIN	
  
  ALTER TABLE [workflow].[audit]  DROP  FK_Workflow_Audit_UserId
      -- drop column
	  ALTER TABLE [WORKFLOW].[Audit]  DROP COLUMN [UserId]
  END 
******************/
